<?php
/**
 *@author sparkguo 
 *@date 2017-09-22   工厂批量订单提醒管理
 */
defined('BASEPATH') OR exit('No direct script access allowed');
class Factorydetail extends CI_Controller {
    
	function __construct(){
		parent::__construct();
		$this->load->model('Function_m','fun');
		$this->load->model('Factory_m','m');
		$this->pageSize =20;
    }
    //统计多批量下单商品 做工厂下单表的统计，时间用订单下单时间段来搜
    function ordercounts(){ 
		$nowpage = $_REQUEST["pageid"];
        if ($nowpage == "") $nowpage = 1;
        $search_data=$_GET;
        $starttime_date=date("Y-m-d",strtotime('-14 day'));//默认最近两周内数据
        $endtime_date=date('Y-m-d');
        $starttime_date=$search_data['starttime']==''?$starttime_date:$search_data['starttime'];
        $endtime_date=$search_data['endtime']==''?$endtime_date:$search_data['endtime'];
        if((strtotime($endtime_date)-strtotime($starttime_date))/86400>7){//搜索时间超过一周，截取最后一周做单周的订单数统计
            $one_week_startdate=date("Y-m-d",strtotime($endtime_date)-7*86400);  
        }else{
            $one_week_startdate=$starttime_date;   
        }
        
        if(in_array($_COOKIE["classid"],array(12,13))){//12工厂1或 13工厂2人员只能看到指定工厂
            $factory_attr=array('12'=>1,'13'=>2);
            $factory_where=' and A.factoryId='.$factory_attr[$_COOKIE["classid"]];
        }
        
        $order_state_where=" and status in(2,3,4) and b.custom=1".$factory_where;
        $one_week_where=" and orderTime between '$one_week_startdate' AND '$endtime_date' ".$order_state_where;
        
        $where = " and orderTime between '$starttime_date' AND '$endtime_date'".$order_state_where;
        
		$one_week_limit=$search_data['one_week_orders']==''?3:$search_data['one_week_orders'];//单周大于多少单条件
        $two_week_limit=$search_data['two_week_orders']==''?5:$search_data['two_week_orders'];
        $sku=$search_data['sku'];//查询单个商品

        $twoweeksorder_list =  $this->fun->getdatabaseslist("db_factory_order A LEFT JOIN db_factory_sell b on A.id=b.orderId","$one_week_where group by b.goods_sku,b.size,b.gender1,b.model
         having count(b.goods_sku)>$one_week_limit");
         //echo $this->db->last_query();
        
        $sku_strs="'000000'";//存储超过1周内超过件数的产品id
        foreach($twoweeksorder_list as $key=>$rs){
            $sku_strs.=",'".$rs['goods_sku']."'";    
        }
        if(!empty($sku)){
            $where.=" and b.goods_sku='$sku'";     
        }
         
        $where.=" and b.goods_sku in($sku_strs)";
        $list = $this->fun->getalllist("db_factory_order A LEFT JOIN db_factory_sell b on A.id=b.orderId",
         $where, $nowpage, 0, $this->pageSize,'group by b.goods_sku,b.size,b.gender1,b.model having count(b.goods_sku)>'.$two_week_limit.' order by A.id desc',
         'A.*,count(b.goods_sku) as sku_count,b.custom,b.price,b.size,b.gender1,b.model,b.goods_sku');
        //echo $this->db->last_query();
		foreach($list["list"] as $key=>$rs){
            $list["list"][$key]["orderTime"] = date("Y-m-d",strtotime($rs["orderTime"]));
            $factorylist =  $this->fun->getdatabasesdetail("db_factory"," AND id='".$rs["factoryId"]."'");
            $list["list"][$key]["factoryl"]  = $factorylist["name"];
            $goodslist = $this->fun->getdatabasesdetail("db_factory_sell A LEFT JOIN db_goods B on A.goods_sku=B.goods_sku"," AND A.orderId='".$rs["id"]."'",'A.goods_sku,A.skuName,B.goods_id');
            $list["list"][$key]["goods_sku"] = $goodslist["goods_sku"];
            $list["list"][$key]["skuName"] = $goodslist["skuName"];
            $goods_images = $this->db->get_where('db_goods_images', "goods_id='".$goodslist["goods_id"]."'")->result_array();
            $thumb_url = $goods_images[0]["thumb_url"];
            if ($thumb_url) {
            	 $list["list"][$key]["thumb_url"] = $thumb_url;
            	 $list["list"][$key]["local_url"] = $goods_images[0]["local_url"];
            } else {
            	$list["list"][$key]["thumb_url"] = "/fuman/images/s-l500.jpg";
            }
            
            $shop =  $this->fun->getdatabasesdetail("d_shop"," AND name='".$rs["shopId"]."'");	//负责人
            $list["list"][$key]["responsible"] = $shop["updateoper"];
        }
		

		$pageList = $this->pagenav->page($list["total"], $nowpage, $this->pageSize);
        $this->tpl->assign('one_week_orders',$one_week_limit);
        $this->tpl->assign('two_week_orders',$two_week_limit);
         $this->tpl->assign('goodsku',$sku);
        $this->tpl->assign('starttime',$starttime_date);
        $this->tpl->assign('endtime',$endtime_date);
        $this->tpl->assign("totalpage", ceil($list["total"] / $this->pageSize));
        $this->tpl->assign("total", $list["total"]);
        $this->tpl->assign("pageList", $pageList);
        $this->tpl->assign("orderlist", $list["list"]);

        $this->tpl->display("factory/ordercounts.tpl");
    }


    function ajaxbutton(){
		$action = $other = $this->input->get_post('op');;
		switch($action){
            case "exportStockGoods":
                $this->exportStockGoods();
            break;
            case "stockGoods":
                $this->stockGoods();
            break;
            case "importGoodsStock":
                $this->importGoodsStock();
            break;
            case "doImportGoodsStock":
                $this->doImportGoodsStock();
            break;
		}
	}

    //查看某一批量的产品的详细订单
    public function tipsOrderList(){
        
      
		$nowpage = $_REQUEST["pageid"];
        if ($nowpage == "") $nowpage = 1;
        $search_data=$this->input->post();
        $starttime_date=$search_data['starttime'];
        $endtime_date=$search_data['endtime'];
        $search_data['size']=$search_data['size']==''?'':$search_data['size'];
        $where = "and orderTime between '$starttime_date' AND '$endtime_date'";
        $where.=" and b.goods_sku=$search_data[sku_id] ";
        $where.=$search_data['size']==''?" and b.size is null ":" and b.size=$search_data[size] ";
        $where.=$search_data['model']==''?" and b.model is null ":" and b.model=$search_data[model] ";
        $where.=$search_data['gender']==''?" and b.gender1 is null ":" and b.gender1=$search_data[gender] ";
        $order_state_where=" and status in(2,3,4)";
        $where.=$order_state_where;
        
		
        $list = $this->fun->getalllist("db_factory_order A LEFT JOIN db_factory_sell b on A.id=b.orderId",
         $where, null, 0, $this->pageSize,'order by A.id desc',
         'A.*,b.custom,b.price,b.size,b.gender1,b.model,b.goods_sku');
              //  echo $this->db->last_query();
        $order_type=array('','新订单','生产中','生产完成','已发货','作废单','禁止发货','加急单','未派单');
		foreach($list["list"] as $key=>$rs){
		    $list["list"][$key]["statustext"]=$order_type[$rs["status"]];
            $list["list"][$key]["orderTime"] = date("Y-m-d",strtotime($rs["orderTime"]));
            $factorylist =  $this->fun->getdatabasesdetail("db_factory"," AND id='".$rs["factoryId"]."'");
            $list["list"][$key]["factoryl"]  = $factorylist["name"];
            $goodslist = $this->fun->getdatabasesdetail("db_factory_sell A LEFT JOIN db_goods B on A.goods_sku=B.goods_sku"," AND A.orderId='".$rs["id"]."'",'A.goods_sku,A.skuName,B.goods_id');
            $list["list"][$key]["goods_sku"] = $goodslist["goods_sku"];
            $list["list"][$key]["skuName"] = $goodslist["skuName"];
            $goods_images = $this->db->get_where('db_goods_images', "goods_id='".$goodslist["goods_id"]."'")->result_array();
            $thumb_url = $goods_images[0]["thumb_url"];
            if ($thumb_url) {
            	 $list["list"][$key]["thumb_url"] = $thumb_url;
            	 $list["list"][$key]["local_url"] = $goods_images[0]["local_url"];
            } else {
            	$list["list"][$key]["thumb_url"] = "/fuman/images/s-l500.jpg";
            }
            
            $shop =  $this->fun->getdatabasesdetail("d_shop"," AND name='".$rs["shopId"]."'");	//负责人
            $list["list"][$key]["responsible"] = $shop["updateoper"];
        }

        $tpl_data=array('ordertype'=>$order_type,'orderlist'=>$list['list']);
        $this->tpl->assign($tpl_data);

        $data=$this->tpl->fetch('factory/tipsOrderList.tpl', $data);
        echo $data;
    }
    
    //查询仓位商品
    public function stockGoods(){
        $this->tpl->assign("warehouses",$this->db->get_where("db_storage",array("isdel"=>0))->result_array());//仓库
        $this->tpl->assign("salestatus", $this->fun->get_sys_goods_status());
        //select distinct substring_index(positions, '.', 1) as positionval from db_goods_stock 
        $stock_list=$this->fun->getdatabaseslist("db_goods_stock"," and positions<>''",
            "distinct substring_index(positions, '.', 1) as positionval,storage_id,storage_name"," order by positions asc");
        
        $this->tpl->assign("stock_list",$stock_list);
        $this->tpl->display("factory/stockGoods.tpl");
    }
    //导出仓位商品
    public function exportStockGoods(){
        $storage=$this->input->post('storage');
        $sale_status=$this->input->post('saletype');//g_sale_status
        $positions=$this->input->post('positions');
        /*if (count($shopname) >0) {
    		$whereshop = implode("','",$shopname);
    	}*/
        if (count($storage) >0) {//仓库信息
    		$storagestr = implode(",",$storage);
    	}
        
        if (count($positions) >0) {//仓位
    		$whereshop = implode(".%' or positions like '",$positions);
    	}else{
    	   die("您没有选择仓位信息 <a href='javascript:history.back()'>返回上页</a>");
    	}
        if($whereshop)$wherestr.=" AND (positions like '".$whereshop.".%')";
        if (count($sale_status) >0) {//销售状态
    		$salesstr = implode(",",$sale_status);
    	}
        if($salesstr)$wherestr.=" AND g_sale_status in(".$salesstr.")";
        if($salesstr)$wherestr2.=" AND g_sale_status in(".$salesstr.")";

        $idwhere=$wherestr;
        $idwhere.=" AND A.storage_id in(1,2) and B.goods_id is not null";
        if($storagestr)$wherestr2.=" AND A.storage_id in(".$storagestr.")";
        
        
        $goods_id_list=$this->fun->getdatabaseslist("db_goods_stock A left join db_goods B on A.goods_id=B.goods_id","$idwhere","B.goods_id,A.positions");
        //dump($goods_id_list);
        //echo $this->db->last_query();
        $goods_id_arr=iarray_column($goods_id_list,"goods_id");
        $g_positions=iarray_column($goods_id_list,"positions",'goods_id');
        //dump($goods_id_str);
        if (count($goods_id_arr) >0) {//销售状态
    		$goods_id_str = implode(",",$goods_id_arr);
    	}else{
    	   $goods_id_str=0;
    	}
        $wherestr2.=" AND A.goods_id in(".$goods_id_str.")";
        
        $goods_list=$this->fun->getdatabaseslist("db_goods_stock A left join db_goods B on A.goods_id=B.goods_id","$wherestr2","*,A.storage_name as storage_name");
        //dump($goods_list);
        //echo $this->db->last_query();
        $this->tpl->assign('total_datas',count($goods_list));  
        $this->tpl->assign("stock_list",$goods_list);
        $this->tpl->assign("g_positions",$g_positions);
        $this->tpl->display("factory/stockGoodsList.tpl");
        
    }
    
    //导入盘点商品
    public function importGoodsStock(){
        $this->tpl->display("factory/importGoodsStock.tpl");
    }
    
    //导入盘点商品
    public function doImportGoodsStock(){

        require dirname(__FILE__)."/../../fuman/ClassesExcel/PHPExcel.php";
        set_time_limit(0);
        $check_day=$this->input->post("selectday");
        $deliverType=$this->input->post("deliverType");
        
        if(!$check_day){
          //  echo "必须选择出货单的出货时间";exit;
        }
        $factory_type_val=2;
        $arr_excel_file = $_FILES['file_excel'];
        
        if(!empty($arr_excel_file) && $arr_excel_file['error']==0) {
            $fileName = $arr_excel_file["name"];
            $tmpFileName = $arr_excel_file["tmp_name"];
            $fileExtName = explode(".", $fileName);
            if (!($fileExtName[1] == "xls" || $fileExtName[1] == "xlsx")) {//扩展名判断
                echo "上传的文件扩展名应该为.xls或.xlsx";
                exit;
            }
            if ($fileExtName[1] == 'xlsx') {
                $objReader = new PHPExcel_Reader_Excel2007();
                $objExcel = $objReader->load($tmpFileName);
            } else if ($fileExtName[1] == 'xls') {
                $objReader = new PHPExcel_Reader_Excel5();
                $objExcel = $objReader->load($tmpFileName);
            }
            // $this->repeatCheck();
            $storage_list=$this->db->get_where("db_storage",array("isdel"=>0))->result_array();//仓库
          
            $storage_list=iarray_column($storage_list,'id','storagename');
          
            $result=$this->checkExpPriec($objExcel);

            //dump($storage_list);
           // print_r($result);
            
            foreach($result as $key=>$stockinfo){
                if($stockinfo['goods_sku']=='盘点库存数' || $stockinfo['goods_id']=='商品id'){
                    continue;
                }
                $rs=$stockinfo['goods_id'];
                $storage_id=$storage_list[$stockinfo['storage_name']];
                $storagename =  $stockinfo["storage_name"];
                $instock[$key]=$stockinfo["newstock"];
                if($stockinfo["newstock"]==='' || $stockinfo["newstock"]===null){//
                    //echo $stockinfo['goods_sku'].'is continue<br />';
                    continue;
                }
				//$detail = $this->fun->getdatabasesdetail('db_goods'," AND goods_id='".$rs."'");
				$oldstock = $this->fun->getdatabasesdetail('db_goods_stock'," and storage_id='".$storage_id."' and goods_id=$rs");
                $stockinfo['stock']=$oldstock['stock'];
				$sql = "UPDATE db_goods_stock set stock='".$instock[$key]."' where storage_id='".$storage_id."' and goods_id=$rs";
				$this->db->query($sql);
				$this->fun->goods_update_log($rs,'批量盘点更新'.$storagename.'库存 ['.$instock[$key].'] 原库存 ['.$stockinfo['stock'].']',
                    1,$stockinfo["stock"],$instock[$key]);	//更新了库存
				$this->fun->goods_stock_log($rs,$rs,0,'库存盘点',$instock[$key],1,$stockinfo["stock"],$instock[$key]);    //存日志
				$post="";
				$post['goods_id'] = $rs;
				$post['goods_name'] = $stockinfo['goods_name'];
				$post['oldnum'] = $stockinfo["stock"];
				$post['newnum'] = $instock[$key];
				$post['goods_sku'] = $stockinfo['goods_sku'];
				$post['goods_instock'] = $instock[$key];
				$post['tostock'] = $storage_id;
				$post['storagename'] = $storagename;
				$post['intime'] = time();
				$post['oper'] = $_COOKIE["userid"];
				$post['opername'] = $_COOKIE["username"];
				$post['memo'] = '批量盘点';
				$this->db->insert('db_goods_inventory',$post); 				
                    
            }            
            echo '批盘点数据更新完成，请至<a href="/purchase/inputstock">盘点日志</a>查看结果';
            //$this->tpl->display('factory/sendGoodsCheck.tpl');//orderlist/chargeslist.tpl
        }else{
            echo '请上传商品盘点数据';
        }
        
    }
    
    public function checkExpPriec($objExcel){
        $sheet = $objExcel->getSheet(0);    
        $rows = $sheet->getHighestRow(); //总行数
        $cols = "L";//$sheet->getHighestColumn(); //总列数 英文大写字母
        $arrCellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X',
            'Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU',
            'AV','AW','AX','AY','AZ');
        $cols_length = array_search($cols, $arrCellName); //获取数组下标最大数
        $arr_deliver = array();
        $str_conn = "";
        $date_colnum=0;
        $goodsName_colnum=3;
        $orderNumber_colnum=1;
        $sku_colnum=2;
        $style_colnum=4;
        $sex_colnum=5;
        $size_colnum=6;
        $numbers_colnum=7;
        $price_colnum=8;
        $data_type=1;
        $data_start_row=1;
                $storage_name_colnum=1;
                $positions_colnum=2;
                $goods_sku_colnum=3;
                $goodsid_colnum=4;
                $goods_name_colnum=5;
                $stock_colnum=6;
                $newstock_colnum=7;
                
        //行
        for($row=$data_start_row;$row<=$rows;$row++){
            $deliverNumber = $sheet->getCell($arrCellName[$orderNumber_colnum].$row)->getValue();


            //列
            for($column=0;$column<= $cols_length;$column++){
                $cellValue = $arrCellName[$column].$row; //A2
                $col_val = $sheet->getCell($cellValue)->getValue();
                $col_val_len = strlen($col_val);
                //$arr_deliver[$row]['data_type']=$data_type;
                //$arr_deliver[$row]['id']=$row;
                if($column==0){
                    $arr_deliver[$row]['id'] = $col_val;
                }
                //日期
                if($column==$storage_name_colnum){
                    $arr_deliver[$row]['storage_name'] = $col_val;
                }
                //名称
                if($column==$positions_colnum){
                    $arr_deliver[$row]['positions'] = $col_val;
                }
                //sku
                if($column==$goods_sku_colnum){
                    $arr_deliver[$row]['goods_sku'] = $col_val;
                }
                if($column==$goodsid_colnum){
                    $arr_deliver[$row]['goods_id'] = $col_val;
                }
                //版形
                if($column==$goods_name_colnum){
                    $arr_deliver[$row]['goods_name'] = $col_val;
                }
                //性别
                if($column==$stock_colnum){
                    $arr_deliver[$row]['stock'] = $col_val;
                }
                //性别
                if($column==$newstock_colnum){
                    $arr_deliver[$row]['newstock'] = $col_val;
                }
            }
        }
        $arr_tmp = array();
        $str_conn = trim($str_conn,",");
        return $arr_deliver;  
    }
    
    public function phpinfo(){
        echo phpinfo();   
    } 
           
    
}
